package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBForestilling 
{
	/**
	 * Initializes instance variables
	 */
	private Connection con;
	
	/** Creates connection to the database and creates an instance of DBForestilling*/
	public DBForestilling()
	{
		con = DbConnection1.getInstance().getDBcon();
	}
	
 /** getAllForestillinger finds all Forestillinger with
  * @param retrieveAssociation (tells weather or not the returned Forestillinger
  *  needs to be associated with a Film and a Sal)
  * @return
  */
	public ArrayList<Forestilling> getAllForestillinger(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}
	
/** getForestilling finds a specific forestilling with
 * @param forestillingId
 * @param retrieveAssociation (tells weather or not the returned Forestillinger
  *  needs to be associated with a Film and a Sal)
 * @return
 */
	public Forestilling getForestilling(int forestillingId, boolean retrieveAssociation)
	{
		String wClause =" forestillingID = '" + forestillingId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	
	/** insertForestilling creates a forestilling SQL query to be inserted into database with
	 * 
	 * @param forestilling
	 * @return
	 * @throws Exception (If Forestilling not created)
	 */
	public int insertForestilling(Forestilling forestilling) throws Exception
	{
		int nextForestillingId = GetMax.getMaxId("Select max (forestillingId) from Forestilling");
		nextForestillingId = nextForestillingId + 1;
		//System.out.println("next forestillingId = " + nextForestillingId);

		int rc = -1;
		String query ="INSERT INTO Forestilling(forestillingId, fremvisningsdato, start, slut, film, sal) VALUES('"+

			nextForestillingId + "','" +
			forestilling.getFremvisningsDato() + "','" +
			forestilling.getStart() + "','" +
			forestilling.getSlut() + "','" +
			forestilling.getFilm().getTitel() + "','" +
			forestilling.getSal().getSalNr() + "')";

		//System.out.println("insert : " + query);
		try
		{ // insert new forestilling
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex)
		{
			//System.out.println("Forestilling ikke oprettet");
			throw new Exception ("Forestilling is not inserted correct");
		}
		return(rc);
	}
	
	/** deleteForestilling removes a Forestilling from database with
	 * 
	 * @param forestillingId
	 * @return
	 */
	public int deleteForestilling(int forestillingId)
	{
		int rc=-1;

		String query ="DELETE FROM Forestilling WHERE ForestillingId = '" + 
				forestillingId + "'";
		//System.out.println(query);
		try
		{ //delete from forestilling
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(Exception ex)
		{
			//System.out.println("Delete exception in Forestilling db: "+ex);
		}
		return(rc);
	}
	
	/** updateForestilling creates an update forestilling SQL query to update a forestilling in the database with
	 * 
	 * @param forestilling
	 * @return
	 */
	public int updateForestilling(Forestilling forestilling)
	{
		Forestilling fObj  = forestilling;
		int rc=-1;

		String query="UPDATE forestilling SET "+
				"fremvisningsdato ='"+ fObj.getFremvisningsDato()+"', "+
				"start ='"+fObj.getStart() + "', " +
				"slut ='"+ fObj.getSlut() + "', " +
				"film ='"+ fObj.getFilm().getTitel() + "', " +
				"sal ='"+ fObj.getSal().getSalNr() + "' " +
				" WHERE forestillingId = '"+ fObj.getForestillingId() + "'";
		//System.out.println("Update query:" + query);
		try
		{ // update Forestilling
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex)
		{
			//System.out.println("Update exception in forestilling db: "+ex);
		}
		return(rc);
	}
	
	/** singleWhere is used when we only want to select one Forestilling
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned Forestillinger
	 *  needs to be associated with a Film and a Sal)
	 * @return
	 */
	private Forestilling singleWhere(String wClause, boolean retrieveAssociation)
	{

		ResultSet results;
		Forestilling fObj = new Forestilling();

		String query = buildQuery(wClause);
		//System.out.println(query);
		try
		{// read the forestilling from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() )
			{	
				fObj = buildForestilling(results);
				//Association is to be build
				stmt.close();
				if(retrieveAssociation)
				{   
					//association to Sal and Film
					DBSal dbSal = new DBSal();
					Sal sal = dbSal.getSal(fObj.getSal().getSalNr(), false);
					//System.out.println("Sal is seleceted ");
					fObj.setSal(sal);
					DBFilm dbfilm = new DBFilm();
					Film film = dbfilm.getFilm(fObj.getFilm().getTitel(), false);
					//System.out.println("Film is seleceted ");
					fObj.setFilm(film);
				}
			}
			else
			{
				fObj = null;
			}
		}//end try
		catch(Exception e)
		{
			//System.out.println("Query exception: "+e);
		}
		return fObj;
	}
	
	/** mischWere is used whenever we want to select more than one forestilling or all forestillinger
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned Forestilling needs to be associated with a Film and a Sal)
	 * @return
	 */
	private ArrayList<Forestilling> miscWhere(String wClause, boolean retrieveAssociation) 
	{
		ResultSet results;
		ArrayList<Forestilling> list = new ArrayList<Forestilling>();

		String query = buildQuery(wClause);

		try
		{	// read the forestilling from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while( results.next() )
			{
				Forestilling fObj = new Forestilling();
				fObj = buildForestilling(results);
				list.add(fObj);
			}//end while
			stmt.close();     
			
			if(retrieveAssociation)
			{   //Film and Sal is to be build as well
				for(Forestilling fObj : list){
					DBFilm dbFilmObj = new DBFilm();
					DBSal dbSalObj = new DBSal();
					Film filmObj = dbFilmObj.getFilm(fObj.getFilm().getTitel(), false);
					Sal salObj = dbSalObj.getSal(fObj.getSal().getSalNr(), false);
					//System.out.println("Film and Sal is seleceted ");

					fObj.setFilm(filmObj);   
					fObj.setSal(salObj);
				}
			}//end if   

		}//end try	
		catch(Exception e)
		{
			//System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * buildQuery uses wClause insert String list of the attributes in a Forestilling and builds the
	 * WHERE part of the query that is to be committed to the SQl database
	 * @param wClause 
	 * @return
	 */
	private String buildQuery(String wClause)
	{
		String query = "SELECT * FROM Forestilling";

		if(wClause.length()>0)
			query=query+" WHERE "+ wClause;
		return query;
	}
	
	/** buildForestilling creates a forestilling object with
	 * 
	 * @param results (used to bring attributes to fObj)
	 * @return
	 */
	private Forestilling buildForestilling(ResultSet results)
	{
		Forestilling fObj = new Forestilling();
		try
		{
			fObj.setForestillingId(results.getInt("forestillingId"));
			fObj.setFremvisningsDato(results.getString("fremvisningsdato"));
			fObj.setStart(results.getString("start"));
			fObj.setSlut(results.getString("slut"));
			fObj.setFilm(new Film(results.getString("film")));
			fObj.setSal(new Sal(results.getByte("sal")));

		}
		catch(Exception e)
		{
			//System.out.println("error in building the forestilling object");
		}
		return fObj;
	}
}